﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DreamIn.Structures.LocalDB.entities;
using System.Data.SqlServerCe;

namespace DreamIn.Structures.LocalDB.DataAccessLayer
{
    class PictureDAL
    {
        public List<PictureE> GetPictures()
        {
            SqlCeConnection conn = DALHelper.Connection;
            try
            {
                SqlCeCommand cmd = new SqlCeCommand("select id_picture, id_album, picturePath, pictureName from picture", conn);
                List<PictureE> result = new List<PictureE>();

                conn.Open();
                SqlCeDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    result.Add(new PictureE()
                    {
                        pictureID = (int)(reader[0]),
                        albumID = (int)reader[1],
                        picturePath = (string)reader[2],
                        pictureName = (string)reader[3]
                    });
                }
                reader.Close();
                return result;
            }
            finally
            {
                conn.Close();
            }
        }

        public List<PictureE> GetPicturesInAlbum(AlbumE album)
        {
            SqlCeConnection conn = DALHelper.Connection;
            try
            {
                SqlCeCommand cmd = new SqlCeCommand("select id_picture, picturePath, pictureName from picture where id_picture='"+album.albumID+"'", conn);
                List<PictureE> result = new List<PictureE>();

                conn.Open();
                SqlCeDataReader reader = cmd.ExecuteReader();

                while (reader.Read())
                {
                    result.Add(new PictureE()
                    {
                        pictureID = (int)(reader[0]),
                        albumID = album.albumID,
                        picturePath = (string)reader[2],
                        pictureName = (string)reader[3]
                    });
                }
                reader.Close();
                return result;
            }
            finally
            {
                conn.Close();
            }
        }

        public void AddPicture(int idAlbum, string path, string name)
        {
            SqlCeConnection conn = DALHelper.Connection;
            try
            {
                SqlCeCommand cmd = new SqlCeCommand("insert into picture (id_album, picturePath, pictureName) values ('" + idAlbum + "', '" + path + "', '" + name + "')", conn);
                cmd.ExecuteNonQuery();
            }
            finally
            {
                conn.Close();
            }
        }

        public void UpdatePicture(PictureE picture)
        {
            SqlCeConnection conn = DALHelper.Connection;
            try
            {
                SqlCeCommand cmd = new SqlCeCommand("update picture set id_album='" + picture.albumID + "', picturePath='" + picture.picturePath + "', pictureName='" + picture.pictureName + "' where id_picture='" + picture.pictureID + "'", conn);
                cmd.ExecuteNonQuery();
            }
            finally
            {
                conn.Close();
            }
        }

        public void DeletePicture(PictureE picture)
        {
            SqlCeConnection conn = DALHelper.Connection;
            try
            {
                SqlCeCommand cmd = new SqlCeCommand("delete from picture where id_picture='" + picture.pictureID + "'", conn);
                cmd.ExecuteNonQuery();
            }
            finally
            {
                conn.Close();
            }
        }
    }
}
